package by.grsu.coctails.db;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import by.grsu.coctails.R;
import by.grsu.coctails.common.utils.GetSystemService;
import by.grsu.coctails.common.utils.Log;
import by.grsu.coctails.db.tables.AmountValuesTable;
import by.grsu.coctails.db.tables.CoctailsCategoryRelationTable;
import by.grsu.coctails.db.tables.CoctailsCategoryTable;
import by.grsu.coctails.db.tables.CoctailsTable;
import by.grsu.coctails.db.tables.CompositionTable;
import by.grsu.coctails.db.tables.IngredientsCategoryTable;
import by.grsu.coctails.db.tables.IngredientsTable;
import by.grsu.coctails.model.Coctail;
import by.grsu.coctails.model.Ingredient;
import by.grsu.coctails.model.db.CoctailDb;
import by.grsu.coctails.model.db.CoctailsCategoryDb;
import by.grsu.coctails.model.db.CoctailsCategoryRelationDb;
import by.grsu.coctails.model.db.CompositionDb;
import by.grsu.coctails.model.db.IngredientCategoryDb;
import by.grsu.coctails.model.db.IngredientDb;

public class DBManager {

	public static final String DB_MANAGER = "++DB_MANAGER++";

	private static final String TAG = DBManager.class.getSimpleName();

	private static final int DATABASE_VERSION = 1;

	private static final String DB_NAME = "coctails.db";

	private DBHelper dbHelper;

	private SQLiteDatabase dbReader;

	private SQLiteDatabase dbWriter;

	private Context mContext;

	public DBManager(final Context context) {
		dbHelper = new DBHelper(context, DB_NAME, DATABASE_VERSION);
		dbReader = dbHelper.getReadableDatabase();
		dbWriter = dbHelper.getWritableDatabase();
		mContext = context;
		try {
			dbHelper.createDataBase();
		} catch (IOException e) {
			Log.e(TAG, mContext.getString(R.string.error_copy_database), e);
		}

	}

	public static DBManager get(Context context) {
		return (DBManager) GetSystemService.get(context, DB_MANAGER);
	}

	public List<Coctail> getAllMyCoctails() {
		String query = "SELECT Coctails.Name,Coctails.DrinkInstruction,Coctails.History,"
				+ "Coctails.Image,Ingredients.Name,Ingredients.Image,"
				+ "IngredientCategory.Name,IngredientCategory.Image,Composition.Count,AmountValues.Value,Ingredients.isHaved "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Ingredients.ID = Composition.IngredientID "
				+ "AND Coctails.ID = Composition.CoctailID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "WHERE Composition.CoctailID IN ( "
				+ "SELECT Coctails.ID "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Composition.CoctailID = Coctails.ID "
				+ "AND Composition.IngredientID = Ingredients.ID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "AND Ingredients.isHaved = 1 ) " + "ORDER BY Coctails.Name";

		return getCoctailsList(query, null);
	}

	public List<Coctail> getMyCoctailsByCategory(String categoryName) {
		String query = "SELECT Coctails.Name,Coctails.DrinkInstruction,Coctails.History,Coctails.Image,Ingredients.Name,Ingredients.Image,"
				+ "IngredientCategory.Name,IngredientCategory.Image,Composition.Count,AmountValues.Value,Ingredients.isHaved "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Ingredients.ID = Composition.IngredientID "
				+ "AND Coctails.ID = Composition.CoctailID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "WHERE Composition.CoctailID IN ( "
				+ "SELECT Coctails.ID FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Composition.CoctailID = Coctails.ID "
				+ "AND Composition.IngredientID = Ingredients.ID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "AND Ingredients.isHaved = 1 ) "
				+ "AND Composition.CoctailID IN ( "
				+ "Select ccr.CoctailID From Coctails_Category_Relation ccr "
				+ "WHERE ccr.CategoryID IN( "
				+ "Select cc.ID from CoctailCategory cc "
				+ "Where cc.Name = ? )) " + "ORDER BY Coctails.Name";

		return getCoctailsList(query, new String[] { categoryName });
	}

	public List<Coctail> getAllCoctails() {
		String query = "SELECT Coctails.Name,Coctails.DrinkInstruction,Coctails.History,"
				+ "Coctails.Image,Ingredients.Name,Ingredients.Image,"
				+ "IngredientCategory.Name,IngredientCategory.Image,"
				+ "Composition.Count,AmountValues.Value,Ingredients.isHaved "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Composition.CoctailID = Coctails.ID "
				+ "AND Composition.IngredientID = Ingredients.ID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "ORDER BY Coctails.Name";
		return getCoctailsList(query, null);
	}

	private List<Coctail> getCoctailsList(String query, String[] queryArgs) {
		Cursor cursor = dbReader.rawQuery(query, queryArgs);
		return getCoctail(cursor);
	}

	public List<Coctail> getCoctailByIngredient(String ingredientName) {
		String query = "SELECT Coctails.Name,Coctails.DrinkInstruction,Coctails.History, "
				+ "Coctails.Image,Ingredients.Name,Ingredients.Image, "
				+ "IngredientCategory.Name,IngredientCategory.Image,Composition.Count,AmountValues.Value,Ingredients.isHaved "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Ingredients.ID = Composition.IngredientID "
				+ "AND Coctails.ID = Composition.CoctailID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "WHERE Composition.CoctailID IN ( "
				+ "SELECT Coctails.ID "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Composition.CoctailID = Coctails.ID "
				+ "AND Composition.IngredientID = Ingredients.ID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "WHERE Ingredients.ID IN( "
				+ "SELECT Ingredients.ID FROM Ingredients "
				+ "WHERE Ingredients.Name = ? ) " + "ORDER BY Coctails.Name)";

		Cursor cursor = dbReader.rawQuery(query,
				new String[] { ingredientName });

		return getCoctail(cursor);
	}

	private List<Coctail> getCoctail(Cursor cursor) {
		List<Coctail> result = new ArrayList<Coctail>();
		List<Ingredient> coctailIngredients = new ArrayList<Ingredient>();
		String lastCoctailName = "";
		String coctailName = "";
		String instruction = "";
		String history = "";
		String image = "";
		String ingredientName;
		String ingredientImage;
		String ingredientCategoryName;
		String ingredientCategoryImage;
		String countIngredient;
		String amountValue;

		while (cursor.moveToNext()) {
			coctailName = cursor.getString(0);
			if (!coctailName.equals(lastCoctailName)) {
				if (coctailIngredients.size() > 0) {
					result.add(new Coctail(lastCoctailName, image, history,
							instruction, coctailIngredients));
					coctailIngredients = new ArrayList<Ingredient>();
				}
				lastCoctailName = coctailName;
				instruction = cursor.getString(1);
				history = cursor.getString(2);
				image = cursor.getString(3);
			}

			ingredientName = cursor.getString(4);
			ingredientImage = cursor.getString(5);
			ingredientCategoryName = cursor.getString(6);
			ingredientCategoryImage = cursor.getString(7);
			countIngredient = cursor.getString(8);
			amountValue = cursor.getString(9);
			Integer isHaved = cursor.getInt(10);

			coctailIngredients.add(new Ingredient(ingredientName,
					ingredientImage, ingredientCategoryName,
					ingredientCategoryImage, countIngredient, amountValue,
					isHaved));

		}
		if (!lastCoctailName.equals("")) {
			result.add(new Coctail(lastCoctailName, image, history,
					instruction, coctailIngredients));
		}
		cursor.close();
		return result;
	}

	public List<Ingredient> getAllMyIngredients() {
		String query = "SELECT i.Name,i.Image,ic.Name,ic.Image,i.isHaved FROM Ingredients i "
				+ "INNER JOIN IngredientCategory ic "
				+ "WHERE ic.ID = i.IngredientCategoryID AND i.isHaved = 1 "
				+ "ORDER BY i.Name";
		return getIngredientList(query, null);
	}

	public List<Ingredient> getMyIngredientsByCategory(String categoryName) {
		String query = "SELECT i.Name,i.Image,ic.Name,ic.Image,i.isHaved FROM Ingredients i "
				+ "INNER JOIN IngredientCategory ic "
				+ "WHERE ic.ID = i.IngredientCategoryID AND i.isHaved = 1 "
				+ "AND ic.Name = ?" + "ORDER BY i.Name";
		return getIngredientList(query, new String[] { categoryName });
	}

	public List<Ingredient> getAllIngredients() {
		String query = "SELECT i.Name,i.Image,ic.Name,ic.Image,i.isHaved FROM Ingredients i "
				+ "INNER JOIN IngredientCategory ic "
				+ "WHERE ic.ID = i.IngredientCategoryID " + "ORDER BY i.Name";
		return getIngredientList(query, null);
	}

	private List<Ingredient> getIngredientList(String query, String[] queryArgs) {
		Cursor cursor = dbReader.rawQuery(query, queryArgs);
		List<Ingredient> listIgredients = new ArrayList<Ingredient>();

		while (cursor.moveToNext()) {
			String ingredientName = cursor.getString(0);
			String ingredientImage = cursor.getString(1);
			String categoryName = cursor.getString(2);
			String categoryImage = cursor.getString(3);
			Integer isHaved = cursor.getInt(4);

			listIgredients.add(new Ingredient(ingredientName, ingredientImage,
					categoryName, categoryImage, isHaved));
		}

		cursor.close();
		return listIgredients;
	}

	public List<IngredientCategoryDb> getAllIngredientsCategory() {
		List<IngredientCategoryDb> result = new ArrayList<IngredientCategoryDb>();

		Cursor cursor = dbReader.query(IngredientsCategoryTable.NAME, null,
				null, null, null, null, IngredientsCategoryTable.NAME_COLUMN);

		while (cursor.moveToNext()) {
			result.add(IngredientsCategoryTable.getIngredientCategory(cursor));
		}

		cursor.close();
		return result;
	}

	public List<Coctail> getCoctailsByCategory(String categoryName) {
		String query = "SELECT Coctails.Name,Coctails.DrinkInstruction,Coctails.History,"
				+ "Coctails.Image,Ingredients.Name,Ingredients.Image,IngredientCategory.Name,"
				+ "IngredientCategory.Image,Composition.Count,AmountValues.Value,Ingredients.isHaved "
				+ "FROM Composition "
				+ "INNER JOIN Coctails,Ingredients,IngredientCategory,AmountValues "
				+ "ON Composition.CoctailID = Coctails.ID "
				+ "AND Composition.IngredientID = Ingredients.ID "
				+ "AND Ingredients.IngredientCategoryID = IngredientCategory.ID "
				+ "AND Composition.AmountID = AmountValues.ID "
				+ "WHERE Coctails.ID IN("
				+ "Select ccr.CoctailID From Coctails_Category_Relation ccr "
				+ "WHERE ccr.CategoryID IN("
				+ "Select cc.ID from CoctailCategory cc "
				+ "Where cc.Name = ? )) " + "ORDER BY Coctails.Name";

		Cursor cursor = dbReader.rawQuery(query, new String[] { categoryName });

		return getCoctail(cursor);
	}

	public List<Ingredient> getIngredientByCategory(String categoryName) {

		String query = "SELECT i.Name,i.Image,ic.Name,ic.Image,i.isHaved "
				+ "FROM Ingredients i " + "INNER JOIN IngredientCategory ic "
				+ "WHERE ic.ID = i.IngredientCategoryID " + "AND ic.Name = ? "
				+ "ORDER BY i.Name";

		return getIngredientList(query, new String[] { categoryName });
	}

	public synchronized long updateMyIngredient(String name, Integer isHaved) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(IngredientsTable.IS_HAVED_COLUMN, isHaved);
		return dbReader.update(IngredientsTable.NAME, contentValues,
				IngredientsTable.NAME_COLUMN + "=?", new String[] { name });
	}

	private Long getAmountValueId(String value) {
		if (value == null) {
			return null;
		}
		Cursor cursor = dbReader.query(AmountValuesTable.NAME,
				new String[] { AmountValuesTable.ID_COLUMN },
				AmountValuesTable.VALUE_COLUMN + "=?", new String[] { value },
				null, null, null);
		if (cursor.moveToFirst()) {
			return Long.valueOf(cursor.getInt(0));
		}

		return null;
	}

	public Long addAmountValue(String value) {
		if (value != null) {
			return addRecoreToDB(AmountValuesTable.NAME,
					AmountValuesTable.getContentValues(value));
		}
		return null;
	}

	private List<String> getAmountValues() {
		List<String> result = new ArrayList<String>();
		Cursor cursor = dbReader.query(AmountValuesTable.NAME,
				new String[] { AmountValuesTable.VALUE_COLUMN }, null, null,
				null, null, null);
		while (cursor.moveToNext()) {
			result.add(cursor.getString(0));
		}

		cursor.close();

		return result;
	}

	public IngredientDb getIngredient(String name) {
		Cursor cursor = dbReader.query(IngredientsTable.NAME, null,
				IngredientsTable.NAME_COLUMN + "=?", new String[] { name },
				null, null, null);
		while (cursor.moveToNext()) {
			// cursor.close();
			return IngredientsTable.getIngredient(cursor);
		}
		return null;
	}

	private long addIngredient(IngredientDb ingredient) {
		if (ingredient.getIngredientCategoryID() == null) {
			if (ingredient.getIngredientCategory() != null) {
				long id = addIngredientCategory(ingredient
						.getIngredientCategory());
				ingredient.setIngredientCategoryID(id);
			} else {
				Log.e(TAG, "ingredientCategory == null");
			}
		}
		return addRecoreToDB(IngredientsTable.NAME,
				IngredientsTable.getContentValues(ingredient));
	}

	private IngredientCategoryDb getIngredientCategory(String categoryName) {
		Cursor cursor = dbReader.query(IngredientsCategoryTable.NAME, null,
				IngredientsCategoryTable.NAME_COLUMN + "=?",
				new String[] { categoryName }, null, null, null);
		if (cursor.moveToFirst()) {
			return IngredientsCategoryTable.getIngredientCategory(cursor);
		}

		return null;
	}

	private Long addIngredientCategory(IngredientCategoryDb ingredientCategory) {
		return addRecoreToDB(IngredientsCategoryTable.NAME,
				IngredientsCategoryTable.getContentValues(ingredientCategory));
	}

	public void addNewCoctail(CoctailDb coctail,
			CoctailsCategoryDb coctailCategory,
			List<IngredientDb> listIngredients) {
		List<CoctailsCategoryDb> list = new ArrayList<CoctailsCategoryDb>();
		list.add(coctailCategory);
		addNewCoctail(coctail, list, listIngredients);

	}

	public void addNewCoctail(CoctailDb coctail,
			List<CoctailsCategoryDb> listCoctailCategory,
			List<IngredientDb> listIngredients) {
		dbWriter.beginTransaction();

		CoctailDb tCoctail = getCoctail(coctail.getName());
		if (tCoctail != null) {
			coctail = tCoctail;
		} else {
			long coctailId = addCoctailToDB(coctail);
			coctail.setId(coctailId);
		}

		for (CoctailsCategoryDb category : listCoctailCategory) {
			CoctailsCategoryDb tCategory = getCoctailCategory(category
					.getName());
			long categoryId;
			if (tCategory == null) {
				categoryId = addCoctailCategory(category);

			} else {
				categoryId = tCategory.getId();
			}

			category.setId(categoryId);

			if (!containCoctailCategoryRelation(coctail.getId(),
					category.getId())) {
				addCoctailCategoryRelation(new CoctailsCategoryRelationDb(
						coctail.getId(), category.getId()));
			}
		}

		List<CompositionDb> compositionList = new ArrayList<CompositionDb>();

		for (IngredientDb ingredient : listIngredients) {
			IngredientCategoryDb ingredientCategory = getIngredientCategory(ingredient
					.getIngredientCategory().getName());
			long ingredientCategoryId;
			if (ingredientCategory == null) {
				ingredientCategoryId = addIngredientCategory(ingredient
						.getIngredientCategory());

			} else {
				ingredientCategoryId = ingredientCategory.getId();
			}
			ingredient.setIngredientCategoryID(ingredientCategoryId);

			IngredientDb tIngredient = getIngredient(ingredient.getName());
			long ingredientId;
			if (tIngredient == null) {
				ingredientId = addIngredient(ingredient);

			} else {
				ingredientId = tIngredient.getId();
			}

			ingredient.setId(ingredientId);

			Long amountValueId = getAmountValueId(ingredient.getAmount()
					.getValue());
			if (amountValueId == null) {
				amountValueId = addAmountValue(ingredient.getAmount()
						.getValue());
			}

			compositionList.add(new CompositionDb(coctail.getId(),
					ingredientId, ingredient.getAmount().getCount(),
					amountValueId));

		}

		if (getComposition(coctail.getId()) == null) {
			addComposition(compositionList);
		}
		dbWriter.setTransactionSuccessful();
		dbWriter.endTransaction();
	}

	private boolean containCoctailCategoryRelation(Long coctailId,
			Long categoryId) {
		Cursor cursor = dbReader.query(CoctailsCategoryRelationTable.NAME,
				null, CoctailsCategoryRelationTable.COCTAIL_ID_COLUMN
						+ "=? AND "
						+ CoctailsCategoryRelationTable.CATEGORY_ID_COLUMN
						+ "=?",
				new String[] { coctailId.toString(), categoryId.toString() },
				null, null, null);
		boolean result = cursor.moveToFirst();
		cursor.close();
		return result;
	}

	private CompositionDb getComposition(Long coctailId) {
		Cursor cursor = dbReader.query(CompositionTable.NAME, null,
				CompositionTable.COCTAIL_ID_COLUMN + "=?",
				new String[] { coctailId.toString() }, null, null, null);
		if (cursor.moveToFirst()) {
			return CompositionTable.getComposition(cursor);
		}

		return null;
	}

	private CoctailDb getCoctail(String name) {
		Cursor cursor = dbReader.query(CoctailsTable.NAME, null,
				CoctailsTable.NAME_COLUMN + "=?", new String[] { name }, null,
				null, null);
		if (cursor.moveToFirst()) {
			return CoctailsTable.getCoctail(cursor);
		}

		return null;
	}

	public List<CoctailsCategoryDb> getAllCoctailCategory() {
		Cursor cursor = dbReader.query(CoctailsCategoryTable.NAME, null, null,
				null, null, null, null);
		List<CoctailsCategoryDb> result = new ArrayList<CoctailsCategoryDb>();

		while (cursor.moveToNext()) {
			result.add(CoctailsCategoryTable.getCoctailCategory(cursor));
		}

		cursor.close();
		return result;
	}

	private List<CoctailsCategoryDb> getCoctailCategory(Long coctailId) {
		Cursor cursor = dbReader.rawQuery("SELECT * FROM CoctailCategory cc "
				+ "WHERE cc.ID IN("
				+ "SELECT ccr.CategoryID FROM Coctails_Category_Relation ccr "
				+ "WHERE ccr.CoctailID = ?)",
				new String[] { coctailId.toString() });

		List<CoctailsCategoryDb> result = new ArrayList<CoctailsCategoryDb>();

		while (cursor.moveToNext()) {
			result.add(CoctailsCategoryTable.getCoctailCategory(cursor));
		}
		cursor.close();
		return result;
	}

	private long addCoctailCategoryRelation(CoctailsCategoryRelationDb relation) {
		return addRecoreToDB(CoctailsCategoryRelationTable.NAME,
				CoctailsCategoryRelationTable.getContentValues(relation));
	}

	private void addComposition(List<CompositionDb> compositionList) {
		for (CompositionDb composition : compositionList) {
			addRecoreToDB(CompositionTable.NAME,
					CompositionTable.getContentValues(composition));
		}

	}

	private Long addCoctailToDB(CoctailDb coctail) {
		return addRecoreToDB(CoctailsTable.NAME,
				CoctailsTable.getContentValues(coctail));
	}

	private CoctailsCategoryDb getCoctailCategory(String categoryName) {
		Cursor cursor = dbReader.query(CoctailsCategoryTable.NAME, null,
				CoctailsCategoryTable.NAME_COLUMN + "=?",
				new String[] { categoryName }, null, null, null);
		if (cursor.moveToFirst()) {
			return CoctailsCategoryTable.getCoctailCategory(cursor);
		}

		return null;
	}

	private Long addCoctailCategory(CoctailsCategoryDb coctailCategory) {
		return addRecoreToDB(CoctailsCategoryTable.NAME,
				CoctailsCategoryTable.getContentValues(coctailCategory));
	}

	private long addRecoreToDB(String tableName, ContentValues contentValues) {
		return dbWriter.insertWithOnConflict(tableName, null, contentValues,
				SQLiteDatabase.CONFLICT_NONE);
	}
	
	public SQLiteDatabase getDbReader(){
		return dbReader;
	}

}
